package org.hyy.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.hyy.base.DatabaseConnection;
import org.hyy.util.StringUtil;
import org.hyy.vo.Pay;
import org.hyy.vo.User;

public class PayDAO {
	private DatabaseConnection dbc;
	private Connection con;				//数据库连接对象
	private PreparedStatement ps;		//预编译对象，用来执行SQL语句
	private ResultSet rs;
	
	/**
	 * 添加定单
	 * @param user
	 * @return
	 */
	public String insertOne(Pay pay){
		//打开数据库
		dbc = new DatabaseConnection();
		con = dbc.getCon();
		//操作数据库
		String sql = "insert into pay values(null,?,?,?,?,?)";
		try {
			ps = con.prepareStatement(sql);
			
			//设置参数
			ps.setString(1, pay.getProid());
			ps.setString(2, "");
			ps.setString(3, pay.getName());
			ps.setDouble(4, pay.getMemberPrice());
			ps.setTimestamp(5, new Timestamp(pay.getPdate().getTime()));
			
			//执行操作
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//关闭数据库
			dbc.close(con);
			dbc = null;
		}
		return "aaaaa";
	}
	
	public List<Pay> showall(){
		//打开数据库
		dbc = new DatabaseConnection();
		con = dbc.getCon();
		Pay pay = null;
		List<Pay> payList = null;
		//操作数据库
		String sql = "select t1.id,t2.name,t1.name,t1.memberPrice,t1.pdate from pay t1, product t2 where t1.proid = t2.id";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			payList = new ArrayList<Pay>();
			while(rs.next()){
				pay = new Pay();
				pay.setId(rs.getInt(1));
				pay.setProname(rs.getString(2));
				pay.setName(rs.getString(3));
				pay.setMemberPrice(rs.getDouble(4));
				pay.setPdate(rs.getDate(5));
				payList.add(pay);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//关闭数据库
			dbc.close(con);
			dbc = null;
		}
		return payList;
	}
	
	public List<Pay> showallByUsername(String username){
		//打开数据库
		dbc = new DatabaseConnection();
		con = dbc.getCon();
		Pay pay = null;
		List<Pay> payList = null;
		//操作数据库
		String sql = "select t1.id,t2.name,t1.name,t1.memberPrice,t1.pdate from pay t1, product t2 where t1.proid = t2.id and t1.name=?";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, username);
			rs = ps.executeQuery();
			payList = new ArrayList<Pay>();
			while(rs.next()){
				pay = new Pay();
				pay.setId(rs.getInt(1));
				pay.setProname(rs.getString(2));
				pay.setName(rs.getString(3));
				pay.setMemberPrice(rs.getDouble(4));
				pay.setPdate(rs.getDate(5));
				payList.add(pay);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//关闭数据库
			dbc.close(con);
			dbc = null;
		}
		return payList;
	}
	
	public boolean delete(int pid){
		boolean flag = false;
		//打开数据库
		dbc = new DatabaseConnection();
		con = dbc.getCon();
		//操作数据库
		String sql = "delete from pay where id=?";
		try {
			ps = con.prepareStatement(sql);
			
			//设置参数
			ps.setInt(1, pid);
			
			//执行操作
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//关闭数据库
			dbc.close(con);
			dbc = null;
		}
		
		return flag;
	}
	
	public boolean login(String username,String password){
		boolean flag = false;
		//打开数据库
		dbc = new DatabaseConnection();
		con = dbc.getCon();
		//操作数据库
		String sql = "select * from user where username = ? and password = ?";
		try {
			ps = con.prepareStatement(sql);
			
			//设置参数
			ps.setString(1, username);
			ps.setString(2, StringUtil.getMD5(password.getBytes()));
			
			//执行操作
			rs = ps.executeQuery();
			flag = rs.next();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//关闭数据库
			dbc.close(con);
			dbc = null;
		}
		
		return flag;
	}
}
